-- 1.查看所有文件组(包括主文件组)
select * from sys.filegroups;
-- 2.查看文件及文件组的语句
select df.name, df.physical_name, df.size, df.growth,
	   f.name filegroup, f.is_default
from sys.database_files df
     join sys.filegroups f on df.data_space_id = f.data_space_id;

-- 3.查看分区方案
select * from sys.partition_schemes;
-- 4.删除分区方案
drop partition scheme pro_cddd_scheme;

--5.查看分区函数
select * from sys.partition_functions;	  
--6.删除分区函数
drop partition function pro_cddd_part_fun;


-- 查看分区在哪个文件组
select fg.name, 分区号=sp.destination_id
from sys.destination_data_spaces sp
     inner join sys.partition_schemes scm on sp.partition_scheme_id = scm.data_space_id
     inner join sys.filegroups fg on fg.data_space_id = sp.data_space_id
where scm.name='pro_cddd_scheme';

-- 查看所有分区方案
select 
    ps.name as partition_scheme_name,
    ps.type_desc as scheme_type,
    ds.destination_id,
    ds.data_space_id,
    fg.name as file_group_name
from sys.partition_schemes ps
     join sys.destination_data_spaces ds on ps.data_space_id = ds.partition_scheme_id
     join sys.filegroups fg on ds.data_space_id = fg.data_space_id
where ps.name = 'pro_cddd_scheme';


-- 7.获取方案id最大值
declare @maxNum int = 1;
-- select @maxNum = isnull(ident_current('cost.pro_check_scheme'), 0);
select @maxNum = 130;

-- 8.创建分区函数
declare @funNum int = 1;
declare @part_id_str nvarchar(max) = '';
declare @part_fun_sql nvarchar(max) = 'create partition function pro_cddd_part_fun(int) as range left for values(';
begin

	while @funNum <= @maxNum
    begin
	   set @part_id_str = concat(@part_id_str, cast(@funNum as varchar(3)), ',');
       set @funNum = @funNum + 1;
    end;

	set @part_id_str = left(@part_id_str, len(@part_id_str) - 1);
	set @part_fun_sql = concat(@part_fun_sql, @part_id_str, ');');

	exec sp_executesql @part_fun_sql;
end;

-- 9.创建分区方案
declare @schemeNum int = 1;
declare @scheme_id_str nvarchar(max) = '';
declare @scheme_sql nvarchar(max) = 'create partition scheme pro_cddd_scheme as partition pro_cddd_part_fun to(';
begin

	while @schemeNum <= (@maxNum + 1)
    begin
	   set @scheme_id_str = concat(concat(@scheme_id_str, 'pcsi_'), cast(@schemeNum as varchar(3)), ',');
       set @schemeNum = @schemeNum + 1;
    end;

	set @scheme_id_str = left(@scheme_id_str, len(@scheme_id_str) - 1);
	set @scheme_sql = concat(@scheme_sql, @scheme_id_str, ');');

	exec sp_executesql @scheme_sql;
end;

-- 创建分区表
create table cost.pro_cost_cal_detail_direct_dev_new(
  id int identity(1,1) not null,
  param_flag_id int  null,
  cal_res_id int  null,
  check_scheme_id int  null,
  organization_id int  null,
  hospital_structure_id int  null,
  serve_item_code nvarchar(50) null,
  number decimal(28,12)  null,
  price decimal(28,12)  null,
  work_id int  null,
  dev_id int  null,
  dev_code nvarchar(50) null,
  dev_name nvarchar(100) null,
  dev_work_min decimal(28,12)  NULL,
  dev_work_ratio decimal(28,12)  NULL,
  dev_work_cost_dep_per_min decimal(28,12)  NULL,
  dev_work_cost_main_per_min decimal(28,12)  NULL,
  dev_work_cal_cost_dep decimal(28,12)  NULL,
  dev_work_cal_cost_main decimal(28,12)  NULL,
  dev_work_cal_cost decimal(28,12)  NULL,
  medical_cost_type int  NULL,
  create_user_code nvarchar(30) NULL,
  create_time datetime  NULL,
  update_user_code nvarchar(30)  NULL,
  update_time datetime  NULL,
  delete_flag tinyint  NULL,
  sink_cost_dep_per_min decimal(28,12)  NULL,
  sink_cost decimal(28,12)  NULL,
  cost_item_code varchar(20) NULL,
  calculate_method int  NULL
) on pro_cddd_scheme(check_scheme_id);

create nonclustered index idx_pro_cost_cal_detail_direct_dev
on cost.pro_cost_cal_detail_direct_dev_new(id);

create nonclustered index idx_non_clu_pro_cost_cal_detail_direct_dev
on cost.pro_cost_cal_detail_direct_dev_new(check_scheme_id, organization_id);

EXEC sp_addextendedproperty
'MS_Description', N'主键',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算参数采集完成标志表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'param_flag_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'项目成本核算主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'cal_res_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算方案主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'check_scheme_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算科室id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'organization_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'院区id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'hospital_structure_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'服务项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'serve_item_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'服务项目数量',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'number'
;

EXEC sp_addextendedproperty
'MS_Description', N'服务项目单价',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'price'
;

EXEC sp_addextendedproperty
'MS_Description', N'作业id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'work_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'设备id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'设备code',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'设备名称',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_name'
;

EXEC sp_addextendedproperty
'MS_Description', N'项目使用分钟数',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_work_min'
;

EXEC sp_addextendedproperty
'MS_Description', N'工作量比例',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_work_ratio'
;

EXEC sp_addextendedproperty
'MS_Description', N'每分钟折旧成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_work_cost_dep_per_min'
;

EXEC sp_addextendedproperty
'MS_Description', N'每分钟维保成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_work_cost_main_per_min'
;

EXEC sp_addextendedproperty
'MS_Description', N'计算后折旧成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_work_cal_cost_dep'
;

EXEC sp_addextendedproperty
'MS_Description', N'计算后维保成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_work_cal_cost_main'
;

EXEC sp_addextendedproperty
'MS_Description', N'计算后成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'dev_work_cal_cost'
;

EXEC sp_addextendedproperty
'MS_Description', N'成本维度',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'medical_cost_type'
;

EXEC sp_addextendedproperty
'MS_Description', N'创建人编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'create_user_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'create_time'
;

EXEC sp_addextendedproperty
'MS_Description', N'修改人编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'update_user_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'修改时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'update_time'
;

EXEC sp_addextendedproperty
'MS_Description', N'删除标记:0.存在,1.已删除',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'delete_flag'
;

EXEC sp_addextendedproperty
'MS_Description', N'每分钟沉没成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'sink_cost_dep_per_min'
;

EXEC sp_addextendedproperty
'MS_Description', N'单位沉没成本',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'sink_cost'
;

EXEC sp_addextendedproperty
'MS_Description', N'成本项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'cost_item_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'计算方法:1303.当量法,1801.时间驱动作业成本法',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new',
'COLUMN', N'calculate_method'
;

EXEC sp_addextendedproperty
'MS_Description', N'项目成本核算—直接资产成本明细表',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_detail_direct_dev_new'

-- 将原始表的数据导入新的分区表中
SET IDENTITY_INSERT cost.pro_cost_cal_detail_direct_dev_new ON;

insert into cost.pro_cost_cal_detail_direct_dev_new(
        id, param_flag_id, cal_res_id, check_scheme_id,
        organization_id, hospital_structure_id, serve_item_code, number,
        price, work_id, dev_id, dev_code,
        dev_name, dev_work_min, dev_work_ratio, dev_work_cost_dep_per_min,
        dev_work_cost_main_per_min, dev_work_cal_cost_dep, dev_work_cal_cost_main, dev_work_cal_cost,
        medical_cost_type, create_user_code, create_time, update_user_code,
        update_time, delete_flag, sink_cost_dep_per_min, sink_cost,
        cost_item_code, calculate_method
       )
select  id, param_flag_id, cal_res_id, check_scheme_id,
        organization_id, hospital_structure_id, serve_item_code, number,
        price, work_id, dev_id, dev_code,
        dev_name, dev_work_min, dev_work_ratio, dev_work_cost_dep_per_min,
        dev_work_cost_main_per_min, dev_work_cal_cost_dep, dev_work_cal_cost_main, dev_work_cal_cost,
        medical_cost_type, create_user_code, create_time, update_user_code,
        update_time, delete_flag, sink_cost_dep_per_min, sink_cost,
        cost_item_code, calculate_method
from cost.pro_cost_cal_detail_direct_dev;

SET IDENTITY_INSERT cost.pro_cost_cal_detail_direct_dev_new OFF;

-- 验证数据是否成功导入到新的分区表中（执行一些查询）
-- select count(1) from cost.pro_cost_cal_detail_direct_dev_new;

-- 删除原始表并将新的分区表重命名为原始表的名称
drop table cost.pro_cost_cal_detail_direct_dev;
exec sp_rename 'cost.pro_cost_cal_detail_direct_dev_new', 'pro_cost_cal_detail_direct_dev';